import pymysql  # 引入py自带库 pymysql
from pymysql import cursors


class HandleDb:
    def __init__(self, database: str, host='localhost', port=3306, user='root', password='', charset='utf8'):
        # database                  #数据库名称
        # host = 'localhost'        #ip地址
        # port = 3306               #数据库端口号
        # user = 'root'             #账号
        # password                  #密码
        # charset = 'utf8'          #编码
        try:
            self.conn = pymysql.connect(host=host, user=user, password=password, database=database, port=port,
                                        charset=charset)
            self.cursor = self.conn.cursor(cursor=cursors.DictCursor)
        except pymysql.err.OperationalError as e:
            print("数据库连接失败!")
            print(e)

    # 查
    def find(self, table: str, fields='*', where=None):
        """
            :param table           数据库表名 必传
            :param fields          字段 字段可以为多个 列表或字符串 默认为*
            :param where           条件语句 包含排序,分组 默认为空字符
            :return data或error    列表或字符串
        """
        if not table:
            return 'error'
        if type(fields) == list:
            f_str = ','.join(fields)
        else:
            f_str = fields
        try:
            if where is None:
                sql = f'select {f_str} from {table}'  # sql语句
            else:
                sql = f'select {f_str} from {table} where {where}'  # sql语句
            result = self.sql(sql)
            if result == 'ok':
                return []
            return result
        except:
            return 'error'

    # 删
    def delete(self, table: str, where: str):
        """
            :param table        数据库表名 必传
            :param where        条件语句 包含排序,分页 默认为空字符串(不传则删除整表)
            :return ok或error   字符串
        """
        if not table:
            return 'error'
        try:
            sql = f'delete from {table} where {where}'  # sql语句
            result = self.sql(sql)
            return result
        except:
            return 'error'

    # 增
    def add(self, table: str, dictfv: dict):
        """
            :param table        数据库表名 必传
            :param dictfv       键值对 键为字段名称,值为字段值 必传
            :return ok或error   字符串
        """
        if not (table and dictfv):
            return 'error'
        v_tup = tuple(dictfv.values())
        k_str = ','.join(list(dictfv.keys()))
        try:
            if len(v_tup) == 1:
                sql = f"insert into {table} ({k_str})  values('{v_tup[0]}')"  # sql语句
            else:
                sql = f"insert into {table} ({k_str})  values {v_tup}"  # sql语句
            result = self.sql(sql)
            return result
        except:
            return 'error'

    # 改
    def update(self, table: str, dictfv: dict, where=''):
        """
            :param table        数据库表名 必传
            :param dictfv       键值对 键为字段名称,值为字段值 必传
            :param where        条件语句 包含排序,分页 默认为空字符串
            :return ok或error   字符串
        """
        if not (table and dictfv):
            return 'error'
        item_ls = []
        for k, v in dictfv.items():
            if isinstance(v, str):
                item_ls.append(f'{k}="{dictfv[k]}"')
            else:
                item_ls.append(f'{k}={dictfv[k]}')
        k_str = ','.join(item_ls)
        try:
            sql = f'update {table} set {k_str} where {where}'  # sql语句
            result = self.sql(sql)
            return result
        except:
            return 'error'

    # sql语句
    def sql(self, sqls: str):
        """
            :param sqls         sql语句
        """
        if not sqls:
            return 'not null'
        try:
            self.cursor.execute(sqls)  # 执行
            data = self.cursor.fetchall()
            self.conn.commit()  # 提交
            if data:
                return data
            else:
                return 'ok'
        except Exception as e:
            print(e)
            print(sqls)
            return 'error'

    def close(self):
        self.cursor.close()  # 关闭游标
        self.conn.close()  # 断开连接
